data rnd; set "/brdis2009"; run; 
data bridge; set "/csb2009_v2016"; run; 
data lbd; set '/lbd2009.sas7bdat'; run;

*/ extract the variables from brdis*/;
data rnd1; set rnd (keep =  id predid einum naics cease_a csedt_a cstat_a   deprc_a capex_a aces_a aloc_a  apdom_a apfg_a appf_a apply_a apptd_a appto_a badto_a bastd_a basto_a bc_a benft_a bremp_a byfed_a devtd_a devto_a dfund_a dwage_a emp_a exp_a fnper_a fosal_a inova_r inovb_r inovc_r inovd_r inove_r  invp_a ipb_a ipa_a ipe_a mater_a  );
year = 2009; 
run; 
data rnd2; set rnd (keep =  id  oaloc_a oapp_a obad_a odev_a rdemp_a rdpt_a phd_a  rdxt_a resf_r revep_a sales_a tcpex_a tedu_a  teng_a texps_a tgovt_a tose_a totrd_a trade_a tsci_a  ventu_a visa_a wages_a wbthp_a  wosal_a wotrd_a wrdem_a wrdxt_a wztot_a ynewp_a ypopp_a yprop_a );
run; 
data rnd1a; set rnd (keep =  id cease_r csedt_r cstat_r   deprc_r capex_r aces_r aloc_r  apdom_r apfg_r appf_r apply_r apptd_r appto_r badto_r bastd_r basto_r bc_r benft_r bremp_r byfed_r devtd_r devto_r dfund_r dwage_r emp_r exp_r fnper_r fosal_r invp_r ipb_r ipa_r ipe_r mater_r  );
run; 
data rnd2a; set rnd (keep =  id  oaloc_r oapp_r obad_r odev_r rdemp_r rdpt_r phd_r  rdxt_r revep_r sales_r tcpex_r tedu_r  teng_r texps_r tgovt_r tose_r totrd_r trade_r tsci_r  ventu_r visa_r wages_r wbthp_r  wosal_r wotrd_r wrdem_r wrdxt_r wztot_r ynewp_r ypopp_r yprop_r );
run;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd1 as L left join rnd2 as R
     on L.id = R.id; 
quit;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd3 as L left join rnd1a as R
     on L.id = R.id; 
quit;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd3 as L left join rnd2a as R
     on L.id = R.id; 
quit;

*/ merge to lbd to get firm id */;
data tome; set rnd( keep = id einum); run;
data tome1; set tome; if einum ~=.; run; 
proc sort data = lbd; by ein; run;
data lbd1; set lbd; if ein = lag(ein) then delete; run; 

proc sql noprint;
     create table del as
     select L.*, R.firmid, R.lbdfid
     from tome1 as L left join lbd1 as R
     on L.einum = R.ein; 
quit;
data del; set del; if firmid =. then delete; run; 

proc sql noprint;
     create table del1 as
     select L.*, R.firmid, R.lbdfid
     from tome as L left join lbd1 as R
     on L.id = R.estabid; 
quit;
data del1; set del1; if firmid =. then delete; run; 

proc append data = del base = del1 force; run; 
proc sort data = del1; by id; run; 
data del1; set del1; if id = lag(id) then delete; run; 

*/ merge to bridge file to get the public status of the firms*/;
proc sql noprint;
     create table rnd2 as
     select L.*, R.*
     from del1 as L left join bridge as R
     on L.firmid = R.firmid_br; 
quit;

data rnd2; set rnd2; 
public = 0; if gvkey ~=. then public = 1; run; 

*/ merge with the entire data*/;
proc sql noprint;
     create table rndful as
     select L.firmid, L.lbdfid, L.public, R.*
     from rnd2 as L left join rnd3 as R
     on L.id = R.id; 
quit;



*/ start  the macro to fill in the data */; 
%macro sample;
%do i= 2010 %to 2018 %by 1;

data rnd; set "/brdis&i."; run; 
data bridge; set "/csb&i._v2016"; run; 
data lbd; set "/lbd&i..sas7bdat"; run;

*/ extract the variables from brdis*/;
data rnd1; set rnd (keep =  id predid einum naics cease_a csedt_a cstat_a   deprc_a capex_a aces_a aloc_a  apdom_a apfg_a appf_a apply_a apptd_a appto_a badto_a bastd_a basto_a bc_a benft_a bremp_a byfed_a devtd_a devto_a dfund_a dwage_a emp_a exp_a fnper_a fosal_a inova_r inovb_r inovc_r inovd_r inove_r  invp_a ipb_a ipa_a ipe_a mater_a  );
year = &i; 
run; 
data rnd2; set rnd (keep =  id  oaloc_a oapp_a obad_a odev_a rdemp_a rdpt_a phd_a  rdxt_a resf_r revep_a sales_a tcpex_a tedu_a  teng_a texps_a tgovt_a tose_a totrd_a trade_a tsci_a  ventu_a visa_a wages_a wbthp_a wosal_a wotrd_a wrdem_a wrdxt_a wztot_a ynewp_a ypopp_a yprop_a );
run; 
data rnd1a; set rnd (keep =  id cease_r csedt_r cstat_r   deprc_r capex_r aces_r aloc_r  apdom_r apfg_r appf_r apply_r apptd_r appto_r badto_r bastd_r basto_r bc_r benft_r bremp_r byfed_r devtd_r devto_r dfund_r dwage_r emp_r exp_r fnper_r fosal_r invp_r ipb_r ipa_r ipe_r mater_r  );
run; 
data rnd2a; set rnd (keep =  id  oaloc_r oapp_r obad_r odev_r rdemp_r rdpt_r phd_r  rdxt_r revep_r sales_r tcpex_r tedu_r  teng_r texps_r tgovt_r tose_r totrd_r trade_r tsci_r  ventu_r visa_r wages_r wbthp_r  wosal_r wotrd_r wrdem_r wrdxt_r wztot_r ynewp_r ypopp_r yprop_r );
run;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd1 as L left join rnd2 as R
     on L.id = R.id; 
quit;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd3 as L left join rnd1a as R
     on L.id = R.id; 
quit;
proc sql noprint;
     create table rnd3 as
     select L.*, R.*
     from rnd3 as L left join rnd2a as R
     on L.id = R.id; 
quit;

*/ merge to lbd to get firm id */;
data tome; set rnd( keep = id einum); run;
data tome1; set tome; if einum ~=.; run; 
proc sort data = lbd; by ein; run;
data lbd1; set lbd; if ein = lag(ein) then delete; run; 

proc sql noprint;
     create table del as
     select L.*, R.firmid, R.lbdfid
     from tome1 as L left join lbd1 as R
     on L.einum = R.ein; 
quit;
data del; set del; if firmid =. then delete; run; 

proc sql noprint;
     create table del1 as
     select L.*, R.firmid, R.lbdfid
     from tome as L left join lbd1 as R
     on L.id = R.estabid; 
quit;
data del1; set del1; if firmid =. then delete; run; 

proc append data = del base = del1 force; run; 
proc sort data = del1; by id; run; 
data del1; set del1; if id = lag(id) then delete; run; 

*/ merge to bridge file to get the public status of the firms*/;
proc sql noprint;
     create table rnd2 as
     select L.*, R.*
     from del1 as L left join bridge as R
     on L.firmid = R.firmid_br; 
quit;

data rnd2; set rnd2; 
public = 0; if gvkey ~=. then public = 1; run; 

*/ merge with the entire data*/;
proc sql noprint;
     create table rnd as
     select L.firmid, L.lbdfid, L.public, R.*
     from rnd2 as L left join rnd3 as R
     on L.id = R.id; 
quit;

proc append data = rnd base = rndful force; run; 

%end; 
%mend sample;
%sample; 

PROC EXPORT DATA= WORK.rndful 
            OUTFILE= "/RND/Initial.dta" 
            DBMS=STATA REPLACE;
RUN;
